package com.zuoye.Service;

import com.sun.xml.internal.ws.client.Stub;
import com.zuoye.Dao.ClaDaoIntImpl;
import com.zuoye.Dao.StuDaoIntImpl;
import com.zuoye.Opjo.Class;
import com.zuoye.Opjo.Student;
import com.zuoye.UBtil.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ClaStuIntIpml implements ClaStuInterface {
    @Override
    public int addClaStu(ArrayList<Class> cla, ArrayList<Student> stu) {
        Connection connection = null;
        try {
            connection = DbUtil.getConnection();
            connection.setAutoCommit(false);//非自动提交
            ClaDaoIntImpl claDaoInt = new ClaDaoIntImpl();
            final ArrayList<Class> claArrayList = new ArrayList<>();
            claArrayList.add(new Class(1, "溜溜", 22, "2021-07-29 15:24:26"));
            int claId = claDaoInt.transaction(connection, claArrayList);

            StuDaoIntImpl stuDaoInt = new StuDaoIntImpl();
            final ArrayList<Student> stuArrayList = new ArrayList<>();
            stuArrayList.add(new Student(100, "岳云鹏3", 36, 170.36, "男", "2021-07-29 15:24:26", claId));
            stuDaoInt.transaction(connection, stuArrayList);
            connection.commit();
        } catch (SQLException throwables) {
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return 0;
    }


    @Override
    public int delClaStu(int cla_id) {
        String sql = "delete cla,stu from cla left join stu on cla.id = stu.cla_id where cla.id =?";
        try (Connection connection = DbUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            int i = 0;
            final Student stu = new Student();
            preparedStatement.setInt(1, stu.getCla_id());
            i = preparedStatement.executeUpdate();
            System.out.println("i = " + i);
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updClaStu(int id, int num) {
        String sql = "update cla c ,stu s set c.num=?  where c.id =(select cla_id from stu where cla_id =?)";
        try (Connection connection = DbUtil.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            int i = 0;
            final Class cla = new Class();
            final Student stu = new Student();
            preparedStatement.setInt(1, cla.getNum());
            preparedStatement.setInt(2, stu.getId());
            i = preparedStatement.executeUpdate();
            System.out.println("i = " + i);
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public ArrayList<?> queClaStu() {
        final ArrayList<Class> list = new ArrayList<>();
        final ArrayList<Student> list1 = new ArrayList<>();

        try (final Connection connection = DbUtil.getConnection();
             final PreparedStatement preparedStatement = connection.prepareStatement("select * from stu s,cla c where s.cla_id = c.id");
             final ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                final Class aClass = new Class();
                final Student student = new Student();
                aClass.setName(resultSet.getString("name"));
                aClass.setNum(resultSet.getInt("num"));
                aClass.setCreatedate(resultSet.getString("createdate"));
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                student.setHeight(resultSet.getDouble("height"));
                student.setSex(resultSet.getString("sex"));
                student.setBirthday(resultSet.getString("birthday"));
                student.setCla_id(resultSet.getInt("cla_id"));
                list.add(aClass);
                list1.add(student);
                System.out.println("aClass = " + aClass);
                System.out.println("student = " + student);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


}
